﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using myproject.method;
using supermarket.info;
using System.Data;

namespace supermarket.method
{
    internal class Classgift
    {
        SqlConnection con = new SqlConnection();
        SqlCommand cmd = new SqlCommand();
        SqlDataReader qlddr = null;  //数据缓冲区
        #region///填加礼品基本信息
        public int giftinfoinsert(Classgiftinfo super)
        {
            int num = 0;
            try
            {
                //1.连接数据库
                Classgetsql sqlcon = new Classgetsql();
                con = sqlcon.getcon();  //数据库已连接并打开
                //2.执行SQL语句（select）
                string s;
                s = "insert into Table_gift values('" + super.Gid + "','" + super.Gname + "','" + super.Gnumber + "','" + super.Gintegral + "','" + super.Imgsrc + "')";
                cmd = new SqlCommand(s, con);
                num = cmd.ExecuteNonQuery();
                return num;

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return -1;
            }
        }

        #endregion

        #region///礼品信息查询，查询条件：礼品名称
        public void giftlselect(string mn, DataGridView dv)
        {
            try
            {
                string s;
                int i = 0;
                //1.连接数据库
                Classgetsql sqlcon = new Classgetsql();
                con = sqlcon.getcon();  //数据库已连接并打开    
                s = "select * from Table_gift where giftname='" + mn + "'";   //根据名称查询
                cmd = new SqlCommand(s, con);   //执行SQL语句
                qlddr = cmd.ExecuteReader();   //把sql语句执行结果读取到数据缓冲区qlddr中
                while (qlddr.Read())
                {
                    dv[0, i].Value = qlddr[0].ToString();  //mid
                    dv[1, i].Value = qlddr[1].ToString();  //mname
                    dv[2, i].Value = qlddr[2].ToString();  //mcode
                    dv[3, i].Value = qlddr[3].ToString();  //mcompany
                    i++;
                }
                qlddr.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
        #endregion

        #region///修改礼品基本信息
        public int giftupdate(string name, Classgiftinfo mone)
        {
            int num = 0;
            try
            {
                string s;
                Classgetsql sqlcon = new Classgetsql();
                con = sqlcon.getcon();  //数据库已连接并打开
                                        //2.执行SQL语句（select）
                s = "update Table_gift set giftname ='" + mone.Gname + "',number ='" + mone.Gnumber + "', integral ='" + mone.Gintegral + "', imgsrc ='" + mone.Imgsrc + "' where id ='" + mone.Gid + "'";
                cmd = new SqlCommand(s, con);
                num = cmd.ExecuteNonQuery();      //受影响的行数
                return num;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                return num = -2;
            }
        }
        #endregion

        #region///礼品信息模糊查询，查询条件：礼品名称
        public void gselect(string name, DataGridView dv)
        {
            try
            {
                string s;
                int i = 0;
                //1.连接数据库
                Classgetsql sqlcon = new Classgetsql();
                con = sqlcon.getcon();  //数据库已连接并打开
                if (name == "")
                {
                    s = "select * from Table_gift";
                }
                else
                {
                    s = "select * from Table_gift where giftname like '%" + name + "%'";
                }
                cmd = new SqlCommand(s, con);   //执行SQL语句
                qlddr = cmd.ExecuteReader();   //把sql语句执行结果读取到数据缓冲区qlddr中

                dv.Rows.Clear();
                while (qlddr.Read())
                {
                    // 动态创建新行
                    DataGridViewRow row = new DataGridViewRow();
                    row.CreateCells(dv); // 创建与 DataGridView 列数相同的单元格
                    row.Height = 100;
                    // 填充数据
                    row.Cells[0].Value = qlddr[0].ToString(); // id
                    row.Cells[1].Value = qlddr[1].ToString(); // giftname
                    row.Cells[2].Value = qlddr[2].ToString(); // number
                    row.Cells[3].Value = qlddr[3].ToString(); // integral

                    // 如果列数大于4且第5列有值，加载图片
                    if (dv.ColumnCount > 4 && !string.IsNullOrEmpty(qlddr[4].ToString()))
                    {
                        string imagePath = qlddr[4].ToString();
                        if (File.Exists(imagePath))
                        {
                            row.Cells[4].Value = System.Drawing.Image.FromFile(imagePath); // imgsrc
                        }
                    }

                    // 将新行添加到 DataGridView
                    dv.Rows.Add(row);
                }
                qlddr.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

        #endregion

        #region///删除礼品基本信息
        public int deletegift(string name)
        {
            int i = 0;
            try
            {
                string s;
                Classgetsql sqlcon = new Classgetsql();
                con = sqlcon.getcon();  //数据库已连接并打开
                                        //2.执行SQL语句（select）
                s = "delete from Table_gift where giftname ='" + name + "'";
                cmd = new SqlCommand(s, con);
                i = cmd.ExecuteNonQuery();      //受影响的行数
                return i;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                return i = -2;
            }
        }
        #endregion

        #region///查询导出的内容
        public DataTable getgiftall()
        {
            DataTable dt = new DataTable();
            try
            {
                string s;
                Classgetsql sqlcon = new Classgetsql();
                con = sqlcon.getcon();  //数据库已连接并打开
                                        //2.执行SQL语句（select）
                s = @"  SELECT [id] as '序号'
      ,[giftname] as '名称'
      ,[number] as '库存'
      ,[integral] as '扣分'
  FROM [super].[dbo].[Table_gift]";
                cmd = new SqlCommand(s, con);
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd);
                sqlDataAdapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                return dt;
            }
        }


        public DataTable getgiftall2()
        {
            DataTable dt = new DataTable();
            try
            {
                string s;
                Classgetsql sqlcon = new Classgetsql();
                con = sqlcon.getcon();  //数据库已连接并打开
                                        //2.执行SQL语句（select）
                s = @"  SELECT *
  FROM [Table_gift]";
                cmd = new SqlCommand(s, con);
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd);
                sqlDataAdapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                return dt;
            }
        }


        public DataTable getgiftall3(string id)
        {
            DataTable dt = new DataTable();
            try
            {
                //数据库已连接并打开
                string s;
                Classgetsql sqlcon = new Classgetsql();
                con = sqlcon.getcon();  

                //2.执行SQL语句（select）
                s = $@"SELECT *
  FROM [Table_gift] where [id]='{id}'";
                cmd = new SqlCommand(s, con);
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd);
                sqlDataAdapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                return dt;
            }
        }


        #endregion


    }
}
